﻿#include "database.h"
#include <QDebug>
#include <QMessageBox>

#include "mainwindow.h"

Database *mydb;

Database::Database()
{
    init();

    //获取客户端ip
    QSqlQuery query = this->query("information_schema.processlist pl", "SUBSTRING_INDEX(host, ':', 1) ip_address"," WHERE pl.info IS NOT NULL");
    if(query.next()){
        client_ip = query.value(0).toString();
    }

//    qDebug() << createGlobalKey("dm_user", "admin", {});
}

bool Database::init()
{
#if 1   //远程
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("10.192.117.230");
   // db.setHostName("10.192.117.86");
    db.setPort(3306);
    db.setDatabaseName("DM_Database");
    db.setUserName("DM_Admin");
    db.setPassword("123456");
#else   //本地
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setPort(3306);
    db.setDatabaseName("DM_Database");
    db.setUserName("root");
    db.setPassword("123456");
#endif

    bool ok = db.open();
    if(ok){
        qDebug() << "连接数据库成功";
        return true;
    }else{
        qDebug() << "连接数据库失败";
        qDebug() << "error open database because" << db.lastError().text();
        QMessageBox::information(NULL, "数据库连接失败！", db.lastError().text());
        return false;
    }
}

//增加系统日志
void Database::addLog(int user_id, QString part, QString op, QString status)
{
    QSqlQuery query;
//    QString ip = "SUBSTRING_INDEX(CURRENT_USER(), '@', -1)";
    QString time= "NOW()";
    QString sql = QString("INSERT INTO dm_log (user_id,operation,content,status,client_ip,create_time) VALUES ('%1','%2','%3','%4','%5', %6);")
            .arg(user_id).arg(part).arg(op).arg(status).arg(client_ip).arg(time);
    qDebug() << sql;
    if(!query.exec(sql)){
        QMessageBox::information(NULL, "数据库连接失败！", db.lastError().text());
    }
}

//通用查询函数
QSqlQuery Database::query(QString table, QString cols, QString where, QString order, QString limit){
    QString sql = QString("SELECT %1 FROM %2%3%4%5;").arg(cols).arg(table).arg(where).arg(order).arg(limit);

    qDebug() << sql;
    QSqlQuery query(sql);
    return query;
}

//查询最大id（已弃用）
int Database::query_max_id(QString table)
{
    QString sql = QString("SELECT MAX(id) FROM %1;").arg(table);
    QSqlQuery query(sql);
    if(query.next()) return query.value(0).toInt();
    return 0;
}

//所有列模糊查询
QString Database::createGlobalKey(QString table, QString key, QStringList cols){
    QString where="(";
    QSqlQuery query;

    if(key.isEmpty() || table.isEmpty()) return "";
    if(!query.exec(QString("SHOW COLUMNS FROM %1;").arg(table))) return "";

    while(query.next()){
        if(cols.indexOf(query.value(0).toString()) == -1)
            where += QString(" OR %1 LIKE '%%2%'").arg(query.value(0).toString()).arg(key);
    }

    where += ")";
    if(where == "()") return "";
    where.replace("( OR ","(");
    return where;
}

QString Database::mapToString(QMap<QString, QString> map)
{
    QMapIterator<QString, QString> iterator(map);
    QStringList list;
    while (iterator.hasNext()) {
        iterator.next();
        list.append(iterator.key() + ":" + iterator.value());
    }
    return list.join(",");
}

QMap<QString, QString> Database::stringToMap(QString string)
{
    QMap<QString, QString> mp;
    QStringList list = string.split(","), tmp;
    if(string.isEmpty()){
        return mp;
    }
    for(int i=0; i<list.size(); i++){
        tmp = list[i].split(":");
        mp.insert(tmp[0], tmp[1]);
    }
    return mp;
}

QSqlQuery Database::SortData(QString table, QString name, bool sort, QString limit_name, QString limit)
{
    QString str;

    if(limit_name==nullptr)
        str=QString("SELECT * FROM %1 ORDER by %2 %3;")
                    .arg(table).arg(name).arg(sort?"ASC":"DESC");
    else
    {
        str=QString("SELECT * FROM %1 WHERE %2 like '%%3%' ORDER by %4 %5;")
                    .arg(table).arg(limit_name).arg(limit).arg(name).arg(sort?"ASC":"DESC");
    }
    QSqlQuery query;

    query.exec(str);
    return query;
}

QSqlQuery Database::ExactMatchQuery(QString table, QString limit_name, QString limit)
{
    QString str=QString("SELECT * FROM %1 WHERE %2 = '%3'")
                .arg(table).arg(limit_name).arg(limit);
    QSqlQuery query;

    query.exec(str);
    return query;
}
